package me.android.library.common.orm.ability;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.google.common.base.Joiner;
import com.google.common.base.Preconditions;
import com.google.common.collect.Lists;

import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import me.android.library.utils.android.SqliteUtil;
import mylab.base.foundation.lang.Identifiable;
import mylab.utils.common.StringUtil;

public interface Select<T extends Identifiable<Long>> extends DS, EntityInfo<T> {

    String COUNT_SQL = "SELECT COUNT(*) FROM %s";
    String SELECT_SQL = "SELECT * FROM %s";

    default long count() {
        String sql = String.format(COUNT_SQL, getTableName());
        try (SQLiteDatabase db = readableDb();
             Cursor cursor = db.rawQuery(sql, null)) {
            if (cursor.moveToFirst()) {
                return cursor.getLong(0);
            } else {
                return 0;
            }
        }
    }

    default long selectCount(String whereClause, String[] whereArgs) {
        EntityInfo.checkWhere(whereClause, whereArgs);
        String sql = String.format(COUNT_SQL, getTableName());
        if (StringUtil.isNotEmpty(whereClause)) {
            sql += " WHERE " + whereClause;
        }

        try (SQLiteDatabase db = readableDb();
             Cursor cursor = db.rawQuery(sql, whereArgs)) {
            if (cursor.moveToFirst()) {
                return cursor.getLong(0);
            } else {
                return 0;
            }
        }
    }

    default T selectById(Long id) {
        String sql = String.format(SELECT_SQL + " WHERE id = ?", getTableName());

        String[] args = new String[]{String.valueOf(id)};
        try (SQLiteDatabase db = readableDb();
             Cursor cursor = db.rawQuery(sql, args)) {
            if (cursor.moveToFirst()) {
                return SqliteUtil.cursorToBean(cursor, getEntityClass());
            } else {
                return null;
            }
        }
    }

    default List<T> selectAll() {
        return selectList(null, null);
    }

    default List<T> selectList(Collection<Long> ids) {
        String where = "id in (?)";
        String idsStr = Joiner.on(",").skipNulls().join(ids.stream().map(String::valueOf).collect(Collectors.toSet()));
        String[] args = new String[]{idsStr};
        return selectList(where, args);
    }

    default List<T> selectList(String whereClause, String[] whereArgs) {
        EntityInfo.checkWhere(whereClause, whereArgs);
        String sql = String.format(SELECT_SQL, getTableName());
        if (StringUtil.isNotEmpty(whereClause)) {
            sql += " WHERE " + whereClause;
        }

        try (SQLiteDatabase db = readableDb();
             Cursor cursor = db.rawQuery(sql, whereArgs)) {
            if (cursor.moveToFirst()) {
                return Lists.newArrayList(SqliteUtil.cursorToBeans(cursor, getEntityClass()));
            } else {
                return Collections.emptyList();
            }
        }
    }

    /**
     * 分页查询<p>
     * 默认使用传统的基于 LIMIT ? OFFSET ? <p>
     */
    default PageResult<T> selectPage(Page page, String whereClause, String[] whereArgs, String orderBy) {
        return selectPage(page, whereClause, whereArgs, orderBy, false);
    }

    /**
     * 分页查询
     *
     * @param page        分页请求参数
     * @param whereClause 查询条件,eg: date > ? and date < ?
     * @param whereArgs   查询条件参数值, 须与查询条件中占位符数是一致
     * @param orderBy     排序条件,eg: id ASC,date DESC
     * @param idBased     是否基于ID排序<p>
     *                    1.传统分页基于offset查询，当offset值很大时存在严重性能问题<p>
     *                    2.基于ID条件查询则不存在性能问题<p>
     *                    3.基于ID条件查询要求：查询条件(whereClause)中须包含 id > ? 或 id < ? 表达式<p>
     *                    4.基于ID条件查询要求：在每次查询执行前动态修改id条件值，故每次查询结果中的总记录数会不一致<p>
     */
    default PageResult<T> selectPage(Page page, String whereClause, String[] whereArgs, String orderBy, boolean idBased) {
        EntityInfo.checkWhere(whereClause, whereArgs);
        if (idBased) {
            boolean hasIdExpr = StringUtil.containsAny(whereClause, "id > ?", "id < ?");
            Preconditions.checkState(hasIdExpr, "基于ID分页须指定id条件表达式: " + whereClause);
        }

        PageResult<T> pageResult = new PageResult<>(page.getPageNumber(), page.getPageSize());

        // 查询总记录数
        int total = (int) selectCount(whereClause, whereArgs);
        int totalPage = (total + page.getPageSize() - 1) / page.getPageSize();
        pageResult.setTotal(total);
        pageResult.setTotalPage(totalPage);

        // 查询当前页数据
        int offset = idBased ? 0 : page.getPageNumber() * page.getPageSize();
        String sql = String.format(SELECT_SQL, getTableName());
        if (StringUtil.isNotEmpty(whereClause)) {
            sql += " WHERE " + whereClause;
        }
        if (StringUtil.isNotEmpty(orderBy)) {
            sql += " ORDER BY " + orderBy;
        }
        sql += " LIMIT ? OFFSET ?";

        // 合并 whereArgs limit offset 参数
        String[] args = new String[whereArgs != null ? whereArgs.length + 2 : 2];
        if (whereArgs != null) {
            System.arraycopy(whereArgs, 0, args, 0, whereArgs.length);
        }
        args[args.length - 2] = String.valueOf(page.getPageSize());
        args[args.length - 1] = String.valueOf(offset);

        //执行查询
        try (SQLiteDatabase db = readableDb();
             Cursor cursor = db.rawQuery(sql, args)) {
            pageResult.addAll(SqliteUtil.cursorToBeans(cursor, getEntityClass()));
        }

        return pageResult;
    }
}
